import pandas as pd
import os

# 第一部分：数据读取和合并
# 1.读取数据
# 会员卡类型
card_type = pd.read_excel('./data/会员卡类型.xlsx', engine='openpyxl')
# 会员卡信息
card_info = pd.read_excel('./data/会员卡信息.xlsx', engine='openpyxl')
# 门店品牌
store_brand = pd.read_excel('./data/门店品牌表.xlsx', engine='openpyxl')
# 销售明细
sale_detail = pd.read_excel('./data/销售明细.xlsx', engine='openpyxl')

# 2.对明细表和会员类型进行合并--merge
merged1 = pd.merge(sale_detail, card_type, left_on=["会员卡类型编码", "会员卡类型"], right_on=["卡类型编码", "卡类型名称"], how='inner')

# 3.删除合并之后不相关的字段(折扣类型编码, 折扣率, 收款方式（暂时不用）, 折扣类型, 月进度, 年进度)--drop()(注意：axis=0-删除行，axis=1-删除列)
merged2 = merged1.drop(["折扣类型编码", "折扣率", "收款方式（暂时不用）", "折扣类型", "月进度", "年进度"], axis=1)
# print(merged2)
# 3.1删除合并之后会员ID字段为空的
merged3 = merged2.dropna(subset=["会员卡ID"])
# 3.2 删除重复行数据
merged4 = merged3.drop_duplicates()

# 4.合并会员卡信息表
merged5 = pd.merge(merged2, card_info, left_on="会员卡ID", right_on="会员id", how='left')
# 4.1 删除 会员id 为空的一行
merged6 = merged5.dropna(subset=["会员id"])

# 5.合并门店品牌表
merged7 = pd.merge(merged6, store_brand, left_on='柜组码', right_on='品牌代码', how='left')

# 6.过滤掉非会员的--merged[]
merged8 = merged7[merged7["会员卡类型"] != "非会员"]

# 第二部分：RFM模型分析
# 1.获取分析基准日期(数据最新日期)--年月日（销售时间） 目的：计算购买时间间隔时长
max_date = merged8['年月日（销售时间）'].max()
# print(max_date)
rfm_data = merged8.groupby(['会员卡类型', '会员卡ID']).agg(
    Recency=('年月日（销售时间）', lambda x: (max_date - x.max()).days),
    Frequency=('柜组码', 'count'),
    Monetary=('销售金额', 'sum'),
    Avg_Discount=('折扣金额', 'mean')
).reset_index()

# 2.计算中位数阙值 目的：对R F M 进行归类划分0，1，一中位数为分界线
r_median = rfm_data['Recency'].median()
f_median = rfm_data['Frequency'].median()
m_median = rfm_data['Monetary'].median()

# 3.生成RFM评分
rfm_data['R'] = rfm_data['Recency'].apply(lambda x: 1 if x < r_median else 0)
rfm_data['F'] = rfm_data['Frequency'].apply(lambda x: 1 if x > f_median else 0)
rfm_data['M'] = rfm_data['Monetary'].apply(lambda x: 1 if x > m_median else 0)

# 4.客户分群
rfm_labels = {
    '111': '重要戒指客户',
    '101': '重要发展客户',
    '011': '重要保持客户',
    '001': '重要挽留客户',
    '110': '一般价值客户',
    '100': '一般发展客户',
    '010': '一般保持客户',
    '000': '一般挽留客户'
}

rfm_data["RFM组合"] = rfm_data['R'].astype(str) + rfm_data['F'].astype(str) + rfm_data['M'].astype(str)
# 通过映射函数map() 对字典的键名映射键值，对出现异常值使用fillna()进行填充
rfm_data["客户类型"] = rfm_data["RFM组合"].map(rfm_labels).fillna('异常客户')

# 5.保存数据到excel表格中去
if not os.path.isfile('RFM分析结果.xlsx'):
    rfm_data.to_excel('RFM分析结果.xlsx', index=False)
# 打印前五行数据
print(rfm_data.head())

